<?php
class System_model extends Model {
	function __construct() {
		parent::Model();
	}
	
	/* Attribute Module */
	function add_attribute($data) {
		$this->db->insert('attributes',$data);
	}
	function delete_attribute($id) {
		$this->db->where('id',$id);
		$this->db->delete('attributes');
	}
	function get_attributes() {
		$attributes = array();
		$this->db->order_by('id','asc');
		$query = $this->db->get('attributes');
		return $query->result_array();
		//$result = $query->result_array();
		/*
		foreach($result as $row)
	    {
			
			//$value = explode("~",$row['value']);
			//$options = array();
			//for($i=0;$i<count($value)-1;$i++) {
				//$options[] = $value[$i];
			//}
			
			$options = array();
			if($row['value'] != '')
			{
				$options = json_decode($row['value'], true);
			}
			$attributes[] = array(
				'id' => $row['id'],
				'name' => $row['name'],
				'options' => $options
			);			
		}
		return $attributes;
		*/
	}
	
	function get_attribute($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('attributes');
		$result = $query->first_row('array');
		//$value = explode("~",$result['value']);
		
		$options = array();
		$options = json_decode($result['value'],true);
		/*
		for($i=0;$i<count($value)-1;$i++) {
			$options[] = $value[$i];
		}
		*/
		return array(
			'id' => $result['id'],
			'name' => $result['name'],
			'options' => $options
		);
	}
	function update_attribute($id,$data) {
		$this->db->where('id',$id);
		$this->db->update('attributes',$data);
	}
	
	/* Shipping Module */
	function get_countries() {
		$this->db->order_by('name','asc');
		$query = $this->db->get('countries');
		return $query->result_array();
	}
	function get_country($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('countries');
		$result = $query->first_row('array'); 
		return $result['name'];
	}
	function get_shipping_countries() {
		$sql = "SELECT DISTINCT `countries`.* FROM `countries`,`shippings_countries`,`shippings`
				WHERE `countries`.`id` = `shippings_countries`.`country_id`
				AND `shippings_countries`.`shipping_id` = `shippings`.`id`
				AND `shippings`.`actived` = 1 ORDER BY `countries`.`id`";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	function get_shippings_country($country_id) {
		$sql = "SELECT `shippings`.* FROM `shippings`,`shippings_countries`
				WHERE `shippings`.`id` = `shippings_countries`.`shipping_id`
				AND `shippings_countries`.`country_id` = $country_id
				AND `shippings`.`actived` = 1
				ORDER BY `default` DESC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	function add_shipping($data) {
		$this->db->insert('shippings',$data);
		return $this->db->insert_id();
	}
	function add_shipping_condition($data) {
		$this->db->insert('shippings_conditions',$data);
	}
	function get_shipping_conditions($shipping_id) {
		$this->db->where('shipping_id',$shipping_id);
		$this->db->order_by('id','asc');
		$query = $this->db->get('shippings_conditions');
		return $query->result_array();
	}
	function add_shipping_country($data) {
		$this->db->insert('shippings_countries',$data);
	}
	function get_shippings() {
		$this->db->order_by('id','asc');
		$query = $this->db->get('shippings');
		return $query->result_array();
	}
	function get_shipping($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('shippings');
		return $query->first_row('array');
	}
	function update_shipping($id,$data) {
		$this->db->where('id',$id);
		$this->db->update('shippings',$data);
	}
	function shipping_country($shipping_id,$country_id) {
		$this->db->where('shipping_id',$shipping_id);
		$this->db->where('country_id',$country_id);
		$query = $this->db->get('shippings_countries');
		if ($query->num_rows() > 0) {
			return true;
		}
		return false;
	}
	function remove_shipping_countries($shipping_id) {
		$this->db->where('shipping_id',$shipping_id);
		$this->db->delete('shippings_countries');
	}
	function remove_shipping_conditions($shipping_id) {
		$this->db->where('shipping_id',$shipping_id);
		$this->db->delete('shippings_conditions');
	}
	function default_shipping($id) {
		$sql = "UPDATE `shippings` SET `default` = 0";
		$this->db->query($sql);
		$sql = "UPDATE `shippings` SET `default` = 1, `actived` = 1 WHERE `id` = $id";
		$this->db->query($sql);
	}
	function active_shipping($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('shippings');
		$shipping = $query->first_row('array');
		$this->db->where('id',$id);
		if ($shipping['actived'] == 0) {			
			$this->db->update('shippings',array('actived' => 1));
		} else if ($shipping['actived'] == 1) {
			$this->db->update('shippings',array('actived' => 0));
		}
	}
	function delete_shipping($id) {
		$this->db->where('id',$id);
		$this->db->delete('shippings');
	}
	
	/* Redeem */
	function check_redeem($id) {
		$this->db->where('customer_id',$id);
		$query = $this->db->get('launchoffer');
		if ($query->num_rows() > 0) {
			return $query->first_row('array');
		}
		return false;
	}
	
	/* Coupon Module */
	function add_coupon($data) {
		$this->db->insert('coupons',$data);
		return $this->db->insert_id();
	}
	function check_coupon_code($code) {
		$this->db->where('code',$code);
		$query = $this->db->get('coupons');
		if ($query->num_rows() > 0) {
			return $query->first_row('array');
		}
		return false;
	}
	function check_coupon_code_update($id,$code) {
		$this->db->where('id != ',$id);
		$this->db->where('code',$code);
		$query = $this->db->get('coupons');
		if ($query->num_rows() > 0) {
			return true;
		}
		return false;
	}
	function get_coupon($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('coupons');
		return $query->first_row('array');
	}
	function get_coupons() {
		$this->db->order_by('id','asc');
		$query = $this->db->get('coupons');
		return $query->result_array();
	}
	function active_coupon($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('coupons');
		$coupon = $query->first_row('array');
		$this->db->where('id',$id);
		if ($coupon['actived'] == 0) {			
			$this->db->update('coupons',array('actived' => 1));
		} else if ($coupon['actived'] == 1) {
			$this->db->update('coupons',array('actived' => 0));
		}
	}
	function update_coupon($id,$data) {
		$this->db->where('id',$id);
		return $this->db->update('coupons',$data);
	}
	function delete_coupon($id) {
		$this->db->where('id',$id);
		$this->db->delete('coupons');
	}
	function check_coupon_period($code,$from_date,$to_date) {
		$sql = "SELECT * FROM `coupons` WHERE `code` = '$code'
				AND DATEDIFF(CURDATE(),DATE('$from_date')) >= 0
				AND DATEDIFF(CURDATE(),DATE('$to_date')) <= 0";
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			return true;
		}
		return false;
	}
	
	/* Keyword */
	function add_keyword($data) {
		$this->db->insert('keywords',$data);
	}
	function get_keyword($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('keywords');
		return $query->first_row('array');
	}
	function most_keyword() {
		$sql = "SELECT id, COUNT(*) as `total` FROM `keywords` GROUP BY `keyword` ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result) {
			$this->db->where('id',$result['id']);
			$query = $this->db->get('keywords');
			$row = $query->first_row('array');
			return to_short($row['keyword'],33).' ('.$result['total'].' times)';
		}
		return 'N/A';
	}
	function most_keywords() {
		$sql = "SELECT id, COUNT(*) as `total` FROM `keywords` GROUP BY `keyword` ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	/* Stats */
	function best_customer() {
		$sql = "SELECT `customer_id`, sum(`total`) as `total` 
				FROM `orders` 
				WHERE `status` = 'success'
				GROUP BY `customer_id` 
				ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result) {
			$this->db->where('id',$result['customer_id']);
			$query = $this->db->get('customers');
			$row = $query->first_row('array');
			if ($row) {
				return to_short($row['firstname'].' '.$row['lastname'],33).' ($'.$result['total'].')';
			}
		}
		return 'N/A';
	}
	function best_customers() {
		$sql = "SELECT `customer_id`, sum(`total`) as `total` 
				FROM `orders` 
				WHERE `status` = 'success'
				GROUP BY `customer_id` 
				ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	function best_category() {
		$sql = "SELECT `products_categories`.`category_id`, count(`products_categories`.`category_id`) as `total`
				FROM `orders`,`carts`,`products_categories` 
				WHERE `orders`.`session_id` = `carts`.`session_id` 
				AND `orders`.`status` = 'successful'
				AND `carts`.`product_id` = `products_categories`.`product_id`
				GROUP BY `carts`.`product_id` 
				ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		$this->db->where('id',$result['category_id']);
		$query = $this->db->get('categories');
		$row = $query->first_row('array');
		return $row['title'].' ('.$result['total'].' items ordered)';
	}
	function best_categories() {
		$sql = "SELECT DISTINCT `products_categories`.`category_id`
				FROM `orders`,`carts`,`products_categories` 
				WHERE `orders`.`session_id` = `carts`.`session_id` 
				AND `orders`.`status` = 'successful'
				AND `carts`.`product_id` = `products_categories`.`product_id`
				GROUP BY `carts`.`product_id`
				ORDER BY count(`products_categories`.`category_id`) DESC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	function best_product() {
		$sql = "SELECT `carts`.`product_id`,sum(`carts`.`quantity`) as `total` 
				FROM `orders`,`carts` 
				WHERE `orders`.`session_id` = `carts`.`session_id` and `orders`.`status` = 'successful'
				GROUP BY `carts`.`product_id` 
				ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result) {
			$this->db->where('id',$result['product_id']);
			$query = $this->db->get('products');
			$row = $query->first_row('array');
			return to_short($row['title'],33).' ('.$result['total'].')';
		}
		return 'N/A';
	}
	function best_products() {
		$sql = "SELECT `carts`.`product_id`,sum(`carts`.`quantity`) as `total` 
				FROM `orders`,`carts` 
				WHERE `orders`.`session_id` = `carts`.`session_id` and `orders`.`status` = 'successful'
				GROUP BY `carts`.`product_id` 
				ORDER BY `total` DESC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}	
	function worst_product() {
		$sql = "SELECT DISTINCT `products`.`id`
				FROM `products` LEFT JOIN (`orders` , `carts`) 
				ON `products`.`id` = `carts`.`product_id`
				AND `carts`.`session_id` = `orders`.`session_id`
				AND `orders`.`status` = 'successful'
				ORDER BY `carts`.`quantity` ASC";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result) {
			$this->db->where('id',$result['id']);
			$query = $this->db->get('products');
			$product = $query->first_row('array');
			
			$sql = "SELECT sum(`carts`.`quantity`) as `total` 
					FROM `carts`,`orders` 
					WHERE `carts`.`product_id` = ".$result['id']." 
					AND `carts`.`session_id` = `orders`.`session_id` 
					AND `orders`.`status` = 'successful'";
			$query = $this->db->query($sql);
			$row = $query->first_row('array');
			$times = 0;
			if ($row['total'] != NULL) { $times = $row['total']; }
			$s = 'time';
			if ($times > 1) { $s .= 's'; }
			return to_short($product['title'],33).' ('.$times.' '.$s.')';
		}
		return 'N/A';
	}
	function worst_products() {
		$sql = "SELECT DISTINCT `products`.`id`
				FROM `products` LEFT JOIN (`orders` , `carts`) 
				ON `products`.`id` = `carts`.`product_id`
				AND `carts`.`session_id` = `orders`.`session_id`
				AND `orders`.`status` = 'successful'
				ORDER BY `carts`.`quantity` ASC";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	/* Email Module */
	function get_email($field,$value) {
		$this->db->where($field,$value);
		$query = $this->db->get('emails');
		return  $query->first_row('array');
	}
	function update_email($id,$data) {
		$this->db->where('id',$id);
		return $this->db->update('emails',$data);
	}
	function add_email($data) {
		$this->db->insert('emails',$data);
		return $this->db->insert_id();
	}
	function delete_emails($field,$value) {
		$this->db->where($field,$value);
		$this->db->delete('emails');
	}
	
	/* Tax Module */
	function add_tax($data) {
		$this->db->insert('taxes',$data);
		return $this->db->insert_id();
	}
	function add_tax_country($data) {
		$this->db->insert('taxes_countries',$data);
		return $this->db->insert_id();
	}	
	function country_has_tax($country_id) {
		$this->db->where('country_id',$country_id);
		$query = $this->db->get('taxes_countries');
		if ($query->num_rows() > 0) {
			return true;
		}
		return false;
	}
	function country_tax($country_id,$tax_id) {
		$this->db->where('country_id',$country_id);
		$this->db->where('tax_id',$tax_id);
		$query = $this->db->get('taxes_countries');
		if ($query->num_rows() > 0) {
			return true;
		}
		return false;
	}	
	function get_taxes() {
		$this->db->order_by('id','asc');
		$query = $this->db->get('taxes');
		return $query->result_array();
	}
	function get_tax($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('taxes');
		return $query->first_row('array');
	}
	function get_tax_country($country_id) {
		$sql = "SELECT `taxes`.* FROM `taxes`,`taxes_countries`
				WHERE `taxes`.`id` = `taxes_countries`.`tax_id`
				AND `taxes`.`actived` = 1
				AND `taxes_countries`.`country_id` = $country_id";
		$query = $this->db->query($sql);
		return $query->first_row('array');
	}
	function delete_tax($id) {
		$this->db->where('id',$id);
		$this->db->delete('taxes');
	}
	function remove_tax_countries($tax_id) {
		$this->db->where('tax_id',$tax_id);
		$this->db->delete('taxes_countries');
	}
	function update_tax($id,$data) {
		$this->db->where('id',$id);
		$this->db->update('taxes',$data);
	}
	function active_tax($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('taxes');
		$tax = $query->first_row('array');
		$this->db->where('id',$id);
		if ($tax['actived'] == 0) {			
			$this->db->update('taxes',array('actived' => 1));
		} else if ($tax['actived'] == 1) {
			$this->db->update('taxes',array('actived' => 0));
		}
	}
	
	function get_banners() {
		$query = $this->db->get('banners');
		return $query->result_array();
	}
	function get_banner($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('banners');
		return $query->first_row('array');
	}
	function add_banner($data) {
		$this->db->insert('banners',$data);
		return $this->db->insert_id();	
	}
	function active_banner($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('banners');
		$banner = $query->first_row('array');
		$this->db->where('id',$id);
		if ($banner['actived'] == 0) {			
			$this->db->update('banners',array('actived' => 1));
		} else if ($banner['actived'] == 1) {
			$this->db->update('banners',array('actived' => 0));
		}
	}
	function update_banner($id,$data) {
		$this->db->where('id',$id);
		$this->db->update('banners',$data);
	}
	function delete_banner($id) {
		$this->db->where('id',$id);
		$this->db->delete('banners');
	}
	function min_lru() {
		$sql = "SELECT min(`lru`) as `min` FROM `banners`";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		$lru = $result['min'];
		if ($lru != NULL) { return $lru; }
		return 0;
	}
	function get_random_banner() {
		$sql = "SELECT * FROM `banners` WHERE `actived` = 1 ORDER BY RAND()";
		$query = $this->db->query($sql);
		return $query->first_row('array');
	}
	function update_lru($banner_id) {
		$this->db->where('id',$banner_id);
		$query = $this->db->get('banners');
		$banner = $query->first_row('array');
		$lru = $banner['lru'] + 1;
		$this->db->where('id',$banner_id);
		$this->db->update('banners',array('lru' => $lru));		
	}
}
?>